Geospatial Analysis with SQL by Bonny P McClain

Author:Bonny P McClain
Language: eng
Format: epub
Publisher: Packt Publishing Pvt Ltd
Published: 2022-12-26T00:00:00+00:00

Figure 4.20 – Reviewing the table catalog in pgAdmin

Understanding how to query datasets with SQL is the first step in introducing prediction models to the database. The execution of these properties will be expanded on as our journey continues.

Exploring the below_poverty_censustract data for Los Angeles County, I want to be able to isolate a tract and explore neighboring tracts. Location and distance might hold clues for exploring marginalized communities or populations living below the poverty line.

Looking for values to explore, I am exploring tracts with a high percentage of the population living below the poverty level. Running the following code to order the data by descending values will help me to find the higher percentages:

SELECT * FROM ch4.below_poverty_censustract ORDER BY below_fpl_pct DESC

I identified the tract of interest by tract number and queried any tracts that intersect. ST_Intersects compares two geometries and returns true if they have any point in common. The following code was written into the query editor in QGIS. Execute the code and name the query layer in the box in Figure 4.21:

SELECT * FROM ch4.below_poverty_censustract WHERE ST_Intersects (ch4.below_poverty_censustract.geom, (SELECT geom FROM ch4.below_poverty_censustract WHERE "tract" = '06037980014'))

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.